// ------------------------------------------------------------------------------------------
// Licensed by Interprise Solutions.
// http://www.InterpriseSolutions.com
// For details on this license please visit  the product homepage at the URL above.
// THE ABOVE NOTICE MUST REMAIN INTACT.
// ------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using InterpriseSuiteEcommerceCommon.InterpriseIntegration.SqlQuery;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace InterpriseSuiteEcommerceCommon.InterpriseIntegration.Admin.QueryBrokers
{
    public class ElectronicDownloadItemQueryBroker : QueryBroker
    {
        private TableSelect _tableSelect = null;
            
        private void EnsureTableSelect()
        {
            if (null == _tableSelect)
            {
                _tableSelect = new TableSelect("InventoryItemMenuView");
                _tableSelect.Alias = "iv";

                IColumn colItemCode = _tableSelect.AddColumn("ItemCode", typeof(string));
                _tableSelect.WithColumn("ItemName");
                _tableSelect.WithColumn("ItemDescription");
                _tableSelect.WithColumn("UnitsInStock");
                IColumn colItemType = _tableSelect.AddColumn("ItemType", typeof(string));
                IColumn colLanguageCode = _tableSelect.AddColumn("LanguageCode", typeof(string));

                LeftOuterJoinTable tblWebDownload = new LeftOuterJoinTable("EcommerceDownload");
                tblWebDownload.Alias = "w";

                _tableSelect.Top = new Top(AppLogic.AppConfigUSInt("AdminInitialListCount"));

                IColumn colDownloadItemCode = tblWebDownload.AddColumn("ItemCode", typeof(string));
                IColumn colFileName = tblWebDownload.AddColumn("FileName", typeof(string));
                IColumn colExtension = tblWebDownload.AddColumn("Extension", typeof(string));
                IColumn colDownloadId = tblWebDownload.AddColumn("DownloadId", typeof(string));
                IColumn colIsActive = tblWebDownload.AddColumn("IsActive", typeof(string));
                tblWebDownload.Qualifier = new EqualLogicalExpression(colItemCode, colDownloadItemCode);

                InnerJoinTable tblInventoryItem = new InnerJoinTable("InventoryItem");
                tblInventoryItem.Alias = "i";
                IColumn colCounter = tblInventoryItem.AddColumn("Counter", typeof(int));
                IColumn colJoinItemCode = tblInventoryItem.AddColumn("ItemCode", typeof(string));
                tblInventoryItem.Qualifier = new EqualLogicalExpression(colItemCode, colJoinItemCode);

                LeftOuterJoinTable tblWebOptionDescription = new LeftOuterJoinTable("InventoryItemWebOptionDescription");
                tblWebOptionDescription.Alias = "iwd";
                IColumn colWebItemCode = tblWebOptionDescription.AddColumn("ItemCode", typeof(string));
                IColumn colWebDescription = tblWebOptionDescription.AddColumn("WebDescription", typeof(string));
                IColumn colWebSiteCode = tblWebOptionDescription.AddColumn("WebSiteCode", typeof(string));
                IColumn colWebLanguageCode = tblWebOptionDescription.AddColumn("LanguageCode", typeof(string));
                tblWebOptionDescription.Qualifier = new EqualLogicalExpression(colItemCode, colWebItemCode);
                ILogicalExpression qualifierWeb = new EqualLogicalExpression(colWebSiteCode, new StringLiteralValue(InterpriseHelper.ConfigInstance.WebSiteCode));
                
                // add it to the result set
                _tableSelect.Columns.Add(colCounter);
                _tableSelect.Columns.Add(colDownloadId);
                _tableSelect.Columns.Add(colFileName);
                _tableSelect.Columns.Add(colExtension);
                _tableSelect.Columns.Add(colIsActive);
                _tableSelect.Columns.Add(colWebDescription);

                _tableSelect.JoinTables.Add(tblInventoryItem);
                _tableSelect.JoinTables.Add(tblWebDownload);
                _tableSelect.JoinTables.Add(tblWebOptionDescription);                

                ILogicalExpression qualifier = null;                

                ILogicalExpression mustMatchOnlyElectronicDownloadItems = new EqualLogicalExpression(colItemType, new StringLiteralValue("Electronic Download"));

                string userCode = HttpContext.Current.User.Identity.Name;
                string languageCode = string.Empty;


                using (SqlConnection con = DB.NewSqlConnection())
                {
                    con.Open();
                    using (IDataReader reader = DB.GetRSFormat(con, "SELECT LanguageCode FROM SystemUserAccount with (NOLOCK) WHERE UserCode = {0}", DB.SQuote(userCode)))
                    {
                        if (reader.Read())
                        {
                            languageCode = DB.RSField(reader, "LanguageCode");
                        }
                    }
                }
                 
                if(!string.IsNullOrEmpty(languageCode))
                {
                    ILogicalExpression mustMatchOnlyCurrentUserLanguageCode = new EqualLogicalExpression(colLanguageCode, new StringLiteralValue(languageCode));
                    ILogicalExpression qualifierWebLan = new EqualLogicalExpression(colWebLanguageCode, new StringLiteralValue(languageCode));
                    qualifier = mustMatchOnlyCurrentUserLanguageCode.Conjunct(new AndConjunction(mustMatchOnlyElectronicDownloadItems));
                    qualifierWeb = qualifierWebLan.Conjunct(new AndConjunction(qualifierWeb));
                }
                else
                {
                    qualifier = mustMatchOnlyElectronicDownloadItems;
                }
                qualifier = qualifier.Conjunct(new AndConjunction(qualifierWeb));
                _tableSelect.Qualifier = qualifier;
            }
        }

        public override TableSelect TableSelect
        {
            get 
            {
                EnsureTableSelect();
                return _tableSelect;
            }
        }
    }
}




